Databases may be used to store and manipulate a wide variety of data, often in various tables. A Relational Database Management System (RDBMS) is a database management system (DBMS) that uses relational techniques for storing and retrieving data. In an RDBMS, data is generally structured into relations, each relation dealing with one or more attributes and comprising one or more objects, or “tuples,” of data. The objects generally associate attribute values with each other. A relation may be visualized as a table, having rows and columns; the columns of the table represent attributes of the relation, and the rows of the table represent individual objects or records that have the related attributes and attribute values. Each row in a table is generally distinguishable from all other rows in the table by at least one column. In database terminology, this column is generally referred to as a “primary key” of the table. In some implementations, the primary key is an integer identifier, such as, for example, a globally unique identifier (GUID).
An RDBMS may have an interface that is designed to receive and process Structured Query Language (SQL) statements. SQL interfaces have evolved into a standard language for RDBMS systems, and SQL has been adopted as such by both the American National Standards Organization (ANSI) and the International Standards Organization (ISO). An SQL interface generally supports various operators with which users can formulate statements to manipulate data in one or more tables and produce new tables as a result. SQL operations may be written as nested relational expressions, where the output of one operation is used as the input to the next operation. Thus, SQL may be used to perform complex operations with a small number of statements.
One of the most common SQL statements executed by RDBMS software is the SELECT statement. A SELECT statement generally has the format: “SELECT <clause> FROM <clause> WHERE <clause>.” The result of such a SELECT statement is a subset of data retrieved by the RDBMS software from one or more existing tables stored in the relational database and specified by the FROM clause. The WHERE clause determines which rows should be returned in the result table and generally includes a search condition to be satisfied by each row returned in the result table. The rows that meet the search condition form an intermediate set, which is then processed further according to specifications in the SELECT clause. The SELECT statement may optionally include a grouping function indicated by a GROUP BY clause. The GROUP BY clause causes the rows in the intermediate result set to be grouped according to the values specified in the clause.
Another operation permitted by SQL is the JOIN operation, which concatenates horizontally all or parts of two or more tables to create a new resulting table. The JOIN operation is implied by naming more than one table in the FROM clause of a SELECT statement.
As mentioned above, queries may be received in an SQL system by an SQL interface. The queries may be rewritten in an SQL processor or compiler from the input format provided by a user into generally standard SQL language. To evaluate a query, an SQL execution plan may be generated by the SQL processor from the rewritten SQL queries and may be provided to an SQL optimizer, which may determine an efficient manner to execute the query. Because of optimization, not every SQL query may be “materialized” by the database system in its entirety; rather, an RDBMS may include substantial logic to avoid materializing intermediate results. Execution of the optimized SQL query generally identifies one or more rows in a database table having data that is responsive to the original query. The one or more rows having data that is responsive to an SQL query that is submitted by a client device may be returned to the client device according to a number of methods.
One method of returning responsive data to a client device is to return a set of primary keys associated with each row. The client device may then submit one or more keys to retrieve the rest of the data included in rows corresponding to the keys. According to this method, access to data that is responsive to an SQL query requires two transactions between the client device and the database system: a first access to obtain primary key(s) for data that is responsive to the SQL query, and a second access to obtain the data associated with the primary key(s).
A second method of returning responsive data to a client device is to use a “cursor structure” that is maintained on the server providing the data. The one or more rows having data that is responsive to the SQL query may be formatted as a temporary table, and a cursor may be instantiated that allows the client device to access records sequentially, based on a specific result pointed to by the cursor. Generally, a cursor is a named control structure used by an application program to point to a row of interest within some set of rows and to retrieve rows from the set, possibly making updates and deletions. An application can then access data on a row-by-row basis from the result table. Two specific kinds of cursors are scrollable cursors and forward-only cursors.
Scrollable cursors may allow an application or client device to fetch rows forward or backward from a current position. Typical scrolling requests include moving one row forward, one row back, to the beginning, or to the end of the result set. A forward-only cursor may allow an application or client device to fetch rows forward serially from the start to end of a result set.
A third method of returning responsive data to a client device is to use a cursor structure that is maintained on the client device. In either the second or third method, a client device may access specific database rows or records by executing instructions that cause the cursor to be advanced or repositioned.